package com.wed.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * This class used : open/close Connect to DB and create Query
 * 
 * @author kobe
 */
public class ConnectDB {

	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;

	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/quanlinhasach";
			// url_sql = "....";
			try {
				this.connect = DriverManager.getConnection(url, "root",
						"23041993");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	/**
	 * ds acount
	 */
	public ArrayList<Acount> getListAcount() {
		String sql = "select * from user";
		ArrayList<Acount> lst = new ArrayList<Acount>();
		try {
			rs = getStatement().executeQuery(sql);
			while (rs.next()) {
				String user = rs.getString("UserName");
				String pass = rs.getString("PassWord");

				Acount sp = new Acount(user, pass);
				lst.add(sp);
			}
		} catch (Exception e) {
		}
		return lst;

	}

	public boolean checkAccount(String userName, String passWord) {
		ArrayList<Acount> listAcc = getListAcount();
		if (userName == null || passWord == null) {
			return false;
		} else {
			for (int i = 0; i < listAcc.size(); i++) {
				if (listAcc.get(i).getUserName().equals(userName)
						&& listAcc.get(i).getPassWord().equals(passWord)) {
					return true;
				}
			}
			return false;
		}

	}

	/**
	 * ds tac gia
	 */
	public ArrayList<TacGia> getAllTacGia() throws Exception {
		ArrayList<TacGia> lst = new ArrayList<TacGia>();
		String strSQL = "select * from tacgia";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				String  matg = rs.getString("MaTG");
				String tentg = rs.getString("TenTG");
				String thongtin = rs.getString("ThongTin");
				
				TacGia tg = new TacGia(matg,tentg,thongtin);
				lst.add(tg);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}
	/**
	 * ds The Loai
	 */
	public ArrayList<TheLoai> getAllTheLoai() throws Exception {

		ArrayList<TheLoai> lst = new ArrayList<TheLoai>();
		String strSQL = "select * from theloai";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				String  matl = rs.getString("MaTL");
				String tentl = rs.getString("TenTheLoai");
				
				
				TheLoai tl = new TheLoai(matl,tentl);
				lst.add(tl);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}
	/**
	 * ds NXB
	 */
	public ArrayList<NXB> getAllNXB() throws Exception {

		ArrayList<NXB> lst = new ArrayList<NXB>();
		String strSQL = "select NXB,TenNXB from nhaxuatban";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				String  manxb = rs.getString("NXB");
				String tennxb = rs.getString("TenNXB");
				
				
				NXB tl = new NXB(manxb,tennxb);
				lst.add(tl);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}
	/**
	 * ds hinh anh
	 */
	public ArrayList<HinhAnh> getImageByID(int masach) throws Exception {
		ArrayList<HinhAnh> lst = new ArrayList<HinhAnh>();
		String sql = "select * from hinhanh where idHinhAnh=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, masach);
		ResultSet rs = pst.executeQuery();
		HinhAnh sp = null;
		while (rs.next()) {
			int ms = Integer.parseInt(rs.getString("idHinhAnh"));
			String hinh = rs.getString("TenHinhAnh");
		
			sp = new HinhAnh(ms, hinh);
			lst.add(sp);
		}
		return lst;
	}

	/**
	 * ds Sach
	 */
	public ArrayList<SanPham> getAllProducts() throws Exception {
		ArrayList<SanPham> lst = new ArrayList<SanPham>();
		String strSQL = "select * from sach";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				int masach = Integer.parseInt(rs.getString("MaSach"));
				String tensach = rs.getString("TenSach");
				String theloai = rs.getString("TheLoai");
				String tacgia = rs.getString("TacGia");
				String nxb = rs.getString("NXB");
				double gianhap = Double.parseDouble(rs.getString("GiaNhap"));
				double giaban = Double.parseDouble(rs.getString("GiaBan"));
				String hinhanh = rs.getString("HinhAnh");
				SanPham sp = new SanPham(masach, tensach, theloai, tacgia, nxb,
						gianhap, giaban);
				lst.add(sp);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}

	public boolean UpDateProduct(String masp, SanPham newsp) throws Exception {
		String sql = "update sach set TenSach=?, TheLoai=?, TacGia=?,NXB=?,GiaNhap=?,GiaBan=?,HinhAnh=? where MaSach=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, newsp.getMaSach());
		pst.setString(2, newsp.getTenSach());
		pst.setString(3, newsp.getTheLoai());
		pst.setString(4, newsp.getTacGia());
		pst.setString(5, newsp.getNXB());
		pst.setDouble(6, newsp.getGiaNhap());
		pst.setDouble(7, newsp.getGiaXuat());
		pst.setString(8, newsp.getHinhAnh());
		return pst.executeUpdate() > 0;
	}

	public boolean insertNew(SanPham sp) throws Exception {
		String sql = "insert into sach(TenSach,TheLoai,TacGia,NXB,GiaNhap,GiaBan) values(?,?,?,?,?,?)";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		
		pst.setString(1, sp.getTenSach());
		pst.setString(2, sp.getTheLoai());
		pst.setString(3, sp.getTacGia());
		pst.setString(4, sp.getNXB());
		pst.setDouble(5, sp.getGiaNhap());
		pst.setDouble(6, sp.getGiaXuat());
		
		return pst.executeUpdate() > 0;

	}

	public boolean deleteProduct(int masach) throws Exception {
		String sql = "delete from sach where MaSach=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, masach);
		return pst.executeUpdate() > 0;
	}

	/**
	 * Used to execute the Insert, Update, Delete statement
	 * 
	 * @param strSQL
	 *            Query VD: Insert into TableName values ('??','??')
	 * @return The number of lines affected by the command
	 */
	public int executeUpdate(String strSQL) throws Exception {
		int result = 0;
		try {
			result = getStatement().executeUpdate(strSQL);
		} catch (Exception ex) {
			throw new Exception(ex.getMessage() + " Error at: " + strSQL);
		} finally {
			this.closeConnet();
		}
		return result;
	}

	public SanPham getProductByID(int MaSach) throws Exception {
		String sql = "select * from sach where MaSach=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, MaSach);
		ResultSet rs = pst.executeQuery();
		SanPham sp = null;
		if (rs.next()) {
			int masach = Integer.parseInt(rs.getString("MaSach"));
			String tensach = rs.getString("TenSach");
			String theloai = rs.getString("TheLoai");
			String tacgia = rs.getString("TacGia");
			String nxb = rs.getString("NXB");
			double gianhap = Double.parseDouble(rs.getString("GiaNhap"));
			double giaban = Double.parseDouble(rs.getString("GiaBan"));
			String hinhanh = rs.getString("HinhAnh");
			sp = new SanPham(masach, tensach, theloai, tacgia, nxb, gianhap,
					giaban);
		}
		return sp;
	}

	public boolean UpdateProduct(int masp, SanPham sp) throws Exception {

		String sql = "update sach set TenSach=?, TheLoai=?,TacGia=?,NXB=?,GiaNhap=?,GiaBan=? where MaSach=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
	
		pst.setString(1, sp.getTenSach());
		pst.setString(2, sp.getTheLoai());
		pst.setString(3, sp.getTacGia());
		pst.setString(4, sp.getNXB());
		pst.setDouble(5, sp.getGiaNhap());
		pst.setDouble(6, sp.getGiaXuat());
		pst.setInt(7,masp);
	
		return pst.executeUpdate() > 0;
	}

	/**
	 * A method to close the connection.
	 * 
	 * @throws SQLException
	 */
	public void closeConnet() throws SQLException {
		if (rs != null && !rs.isClosed())
			rs.close();
		if (stmt != null)
			stmt.close();
		if (connect != null)
			connect.close();
	}

	public static void main(String[] args) throws Exception {
		 SanPham sp=new SanPham("nhaptu","TL6","TG1","NXB1",555,283);
		// if(new ConnectDB().deleteProduct("d"))
		ConnectDB db=new ConnectDB();
		db.UpdateProduct(18, sp);
		ArrayList<NXB>b=new ConnectDB().getAllNXB();
		for(NXB n: b)
		{
		System.out.println(n.getTenNXB());
		System.out.println(n.getEmail());
		}
	}
}
